#!/usr/local/bin/python
# -*- coding: utf-8 -*-

from datetime import datetime

from sqlalchemy import Column, Integer, Float, Text, DateTime, and_, Boolean
from sqlalchemy.orm import declarative_base

import init
import initAlipayPage
from init import session
from initLogger import logger

Base = declarative_base()





class AlipayBillAlchemy(Base):
    '''
    类功能：存储支付宝账单
    '''

    __tablename__ = "alipay_bill"

    '''
    函数功能：
    '''

    id = Column(Integer, primary_key=True, autoincrement=True, comment="id号")
    year = Column(Integer, comment="账单年份")
    month = Column(Integer, comment="账单月份")
    income_expenses = Column(Text, comment="收&支")
    trade_object = Column(Text, comment="交易对方")
    trade_account = Column(Text, comment="对方账号")
    trade_description = Column(Text, comment="商品描述")
    receipt_pay_type = Column(Text, comment="收/付款方式")
    value = Column(Float, comment="金额")
    trade_status = Column(Text, comment="交易状态")
    trade_type = Column(Text, comment="交易分类")
    trade_order_no = Column(Text, comment="交易订单号")
    business_order_no = Column(Text, comment="商家订单号")
    trade_time = Column(DateTime, comment="交易时间")
    bill_type = Column(Text, comment="账单类型")
    matched = Column(Boolean, comment="标记是否符合规则")



    def __init__(self,
                    incomeExpenses="",
                    tradeObject="",
                    tradeAccount="",
                    tradeDescription="",
                    receiptPayType="",
                    value=0.0,
                    tradeStatus="",
                    tradeType="",
                    tradeOrderNo="",
                    businessOrderNo="",
                    tradeDateTime=datetime.now(),
                    billType="正常账单"):

        #tradeDataTime = datetime.strptime(tradeTimeStr, "%Y/%m/%d %H:%M:%S")
        self.year = tradeDateTime.year
        self.month = tradeDateTime.month
        self.income_expenses = incomeExpenses
        self.trade_object = tradeObject
        self.trade_account = tradeAccount
        self.trade_description = tradeDescription
        self.receipt_pay_type = receiptPayType
        self.value = value
        self.trade_status = tradeStatus
        self.trade_type = tradeType
        self.trade_order_no = tradeOrderNo
        self.business_order_no = businessOrderNo
        self.trade_time = tradeDateTime
        self.bill_type = billType

        self.matched = False #用于标注该账单是否匹配解析规则，以确保每条bill只匹配一条规则。



    def AddAlipayBillItem(self):
        '''
        函数功能：添加一条数据
        :return:
        '''
        session.add(self)
        session.commit()

    def UpdateAlipayBillItem(self, id):
        '''
        函数功能；更新一条数据
        :return:
        '''


    def SearchAlipayBillByMonth(self, year, month):
        '''
        函数功能：按照条件查询数据
        :param year:
        :param month:
        :return:
        '''

        alipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year == year,
                                                                        AlipayBillAlchemy.month == month)).all()

        return alipayBillInfo

    def SearchAlipayBillByMonthAndType(self, year, month, billType):
        '''
        函数功能：按照条件查询数据
        :param year:
        :param month:
        :return:
        '''

        if billType == initAlipayPage.HUA_BEI_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year == year,
                                                                          AlipayBillAlchemy.month == month,
                                                                          AlipayBillAlchemy.receipt_pay_type=="花呗")).all()
        if billType == initAlipayPage.CASH_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year == year,
                                                                          AlipayBillAlchemy.month == month,
                                                                          AlipayBillAlchemy.receipt_pay_type != "花呗")).all()

        if billType == initAlipayPage.LARGE_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year == year,
                                                                          AlipayBillAlchemy.month == month,
                                                                          AlipayBillAlchemy.value > initAlipayPage.ALIPAY_BILL_LARGE_VALUE)).all()

        if billType == initAlipayPage.UNUSUAL_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year == year,
                                                                          AlipayBillAlchemy.month == month,
                                                                          AlipayBillAlchemy.bill_type == initAlipayPage.NORMAL_BILL_TYPE)).all()
            '''
            alipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year == year,
                                                                          AlipayBillAlchemy.month == month,
                                                                          AlipayBillAlchemy.bill_type != initAlipayPage.NORMAL_BILL_TYPE)).all()
            '''
        if billType == initAlipayPage.INVALID_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year == year,
                                                                          AlipayBillAlchemy.month == month,
                                                                          AlipayBillAlchemy.bill_type == initAlipayPage.INVALID_BILL_TYPE)).all()


        if billType == initAlipayPage.ALL_BILL_RADIO_TYPE:
            alipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year == year,
                                                                              AlipayBillAlchemy.month == month)).all()


        return alipayBillInfo
    def DelAlipayBillByMonth(self, year, month):
        '''
        函数功能，删除指定月份的张博数据
        :param year:
        :param month:
        :return:
        '''

        deleteAlipayBillInfo = session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year==year,
                                                                     AlipayBillAlchemy.month==month))
        deleteAlipayBillInfo.delete()
        session.commit()

    def TurnbackBillState(self, year, month):
        '''
        函数功能：对指定月份的账单标记进行回退
        :param year:
        :param month:
        update alipay_bill set bill_type="正常账单",  matched=False where year==2024
        :return:
        '''
        '''
            user_obj = db_session.query(User).filter(User.id == 179074001).update({"name":"XXX"})
            db_session.commit()            
        '''
        session.query(AlipayBillAlchemy).filter(and_(AlipayBillAlchemy.year==year, AlipayBillAlchemy.month==month)) \
                                        .update({AlipayBillAlchemy.bill_type:"正常账单", AlipayBillAlchemy.matched:False})
        session.commit()

def main():

    '''
    alipayBillAlchemy = AlipayBillAlchemy(  incomeExpenses="支出",
                                            tradeObject="北京轨道交通路网管理有限公司",
                                            tradeAccount="zhi***@bmncc.com.cn",
                                            tradeDescription="9月30日18:32 - 北京轨道交通扫码乘车",
                                            receiptPayType="花呗",
                                            value="2.0",
                                            tradeStatus="交易成功",
                                            tradeType="交通出行",
                                            tradeOrderNo="2022093022001496501424562114",
                                            businessOrderNo="20220930185005261125",
                                            tradeDateTime=datetime.now()
                                          )

    alipayBillAlchemy.AddAlipayBillItem()
    '''

    alipayBillAlchemy = AlipayBillAlchemy()
    alipayBillAlchemy.DelAlipayBillByMonth(2022,9)

#创建数据库表
AlipayBillAlchemy.metadata.create_all(init.engine)


if __name__ == '__main__':
    main()